package com.asiainfo.fc.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.asiainfo.fc.entity.ProxyInfo;
import com.asiainfo.fc.entity.VoteInfo;

public class DBHelper {

	private static DBHelper helper = null;

	private DBHelper() {
	}

	/**
	 * get instance
	 * 
	 * @return
	 */
	public static synchronized DBHelper getInstance() {

		if (helper == null)
			helper = new DBHelper();

		return helper;
	}

	static {
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}
	}

	/***
	 * restoreDb
	 * 
	 * @param voteList
	 * @throws SQLException
	 */
	public boolean restoreDb(ArrayList<VoteInfo> voteList) {
		try {
			Connection conn = DriverManager
					.getConnection("jdbc:sqlite:vote.db");
			Statement stat = conn.createStatement();
			stat.executeUpdate("drop table if exists tb_vote;");
			StringBuffer sBuffer = new StringBuffer();
			sBuffer.append("CREATE TABLE tb_vote (");
			sBuffer.append("		voteId  INTEGER NOT NULL,");
			sBuffer.append("		voteNumber   TEXT NOT NULL,");
			sBuffer.append("		v7   NOT NULL,");
			sBuffer.append("		v6   NOT NULL,");
			sBuffer.append("		v5   NOT NULL,");
			sBuffer.append("		v4   NOT NULL,");
			sBuffer.append("		v3   NOT NULL,");
			sBuffer.append("		v2   NOT NULL,");
			sBuffer.append("		v1  INTEGER NOT NULL,");
			sBuffer.append("	PRIMARY KEY (voteId ASC)");
			sBuffer.append(");");

			stat.executeUpdate(sBuffer.toString());
			PreparedStatement prep = conn
					.prepareStatement("insert into tb_vote values (?,?,?,?,?,?,?,?,?);");

			for (VoteInfo voteInfo : voteList) {
				prep.setInt(1, voteInfo.getVoteId());
				prep.setString(2, voteInfo.getVoteNumbers());
				String strVotes = voteInfo.getVoteNumbers().trim();
				// 05,09,12,29,30,31;05
				int index = strVotes.indexOf(";");
				String[] arrays = strVotes.substring(0, index).split(",");
				String lastVote = strVotes.substring(index + 1);
				index = 0;
				for (String str : arrays) {
					prep.setInt(3 + index, Integer.parseInt(str));
					index++;
				}
				prep.setInt(3 + index, Integer.parseInt(lastVote));
				prep.addBatch();

			}
			conn.setAutoCommit(false);
			prep.executeBatch();
			conn.setAutoCommit(true);

			conn.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/***
	 * update Db
	 * 
	 * @param voteList
	 * @throws SQLException
	 */
	public boolean updateDb(ArrayList<VoteInfo> voteList) {
		try {
			Connection conn = DriverManager
					.getConnection("jdbc:sqlite:vote.db");
			PreparedStatement prep = conn
					.prepareStatement("insert into tb_vote values (?,?,?,?,?,?,?,?,?);");
			ArrayList<VoteInfo> existList = queryByList(voteList);
			if (existList != null) {
				for (VoteInfo vo : existList) {
					VoteInfo voteInfo = findIndex(voteList, vo.getVoteId());
					voteList.remove(voteInfo);
				}
				existList.clear();
				existList = null;

			}
			for (VoteInfo voteInfo : voteList) {
				prep.setInt(1, voteInfo.getVoteId());
				prep.setString(2, voteInfo.getVoteNumbers());
				String strVotes = voteInfo.getVoteNumbers().trim();
				// 05,09,12,29,30,31;05
				int index = strVotes.indexOf(";");
				String[] arrays = strVotes.substring(0, index).split(",");
				String lastVote = strVotes.substring(index + 1);
				index = 0;
				for (String str : arrays) {
					prep.setInt(3 + index, Integer.parseInt(str));
					index++;
				}
				prep.setInt(3 + index, Integer.parseInt(lastVote));
				prep.addBatch();

			}
			conn.setAutoCommit(false);
			prep.executeBatch();
			conn.setAutoCommit(true);

			conn.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	private VoteInfo findIndex(ArrayList<VoteInfo> listInfo, int voID) {
		for (VoteInfo voteInfo : listInfo) {
			if (voteInfo.getVoteId() == voID)
				return voteInfo;
		}
		return null;
	}

	/***
	 * query Db
	 * 
	 * @param voteList
	 * @throws SQLException
	 */
	public ArrayList<VoteInfo> queryByList(ArrayList<VoteInfo> voteList) {
		Connection conn = null;
		try {
			ArrayList<VoteInfo> vList = new ArrayList<VoteInfo>();
			conn = DriverManager.getConnection("jdbc:sqlite:vote.db");
			String sql = "select * from  tb_vote  where 1=1  ";
			if (voteList != null && voteList.size() > 0) {
				String dot = ",";
				sql += " and voteid in (";
				for (int i = 0; i < voteList.size(); i++) {
					VoteInfo vo = voteList.get(i);
					int vId = vo.getVoteId();
					if (i == voteList.size() - 1)
						dot = "";
					sql += vId + dot;
				}
				sql += " );";
			}
			sql += " order by voteid desc";

			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				VoteInfo vote = new VoteInfo();
				vote.setVoteId(rs.getInt("voteid"));
				vote.setVoteNumbers(rs.getString("voteNumber"));
				vList.add(vote);
			}
			rs.close();
			stmt.close();
			conn.close();
			return vList;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {

					e.printStackTrace();
				}
				conn = null;
			}
		}
	}

	/**
	 * get proxy
	 * 
	 * @return
	 */
	public ProxyInfo getProxy() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection("jdbc:sqlite:vote.db");
			String sql = "select * from  tb_proxy  where 1=1  ";

			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ProxyInfo info = null;
			if (rs.next()) {
				info = new ProxyInfo();
				info.setIsuse(rs.getInt("isUse"));
				info.setProxyUserName(rs.getString("proxyUserName"));
				info.setProxyPassWord(rs.getString("proxyPassWord"));
				info.setProxyHost(rs.getString("proxyHost"));
				info.setProxyPort(rs.getInt("proxyPort"));
			}
			rs.close();
			stmt.close();

			return info;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {

					e.printStackTrace();
				}
				conn = null;
			}
		}
	}

	/**
	 * save proxy
	 * 
	 * @param info
	 * @return
	 */
	public boolean saveProxy(ProxyInfo info) {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection("jdbc:sqlite:vote.db");
			Statement stat = conn.createStatement();
			stat.executeUpdate("delete  from   tb_proxy;");

			PreparedStatement prep = conn
					.prepareStatement("insert into tb_proxy values (?,?,?,?,?);");

			prep.setString(1, info.getProxyHost());
			prep.setInt(2, info.getProxyPort());
			prep.setString(3, info.getProxyUserName());
			prep.setString(4, info.getProxyPassWord());
			prep.setInt(5, info.getIsuse());

			prep.addBatch();

			conn.setAutoCommit(false);
			prep.executeBatch();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {

					e.printStackTrace();
				}
				conn = null;
			}
		}

	}
}
